package etf.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Random;


import etf.library.*;


public class ComplexJDBC {
	private Connection connection;
	
	public ComplexJDBC(){
		connection = JDBCUtil.getConnection();
		JDBCUtil.prepareStatements(connection);
	}
	
	public long insert(int size) {
		
		ResultSet genKeys = null;
		ResultSet genKeysMember = null;
		ResultSet genKeysBook = null;
		ResultSet genKeysEmp = null;
		
		int libraryId = -1;
		int authorId = -1;
		int addressId = -1;
		int categoryId = -1;
		int memberId = -1;
		int bookId = -1;
		int empId = -1;
		long elapsedTime = 0;
		try {

			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();

			for (int i = 0; i < size/100; i++) {
				Address address = new Address("Splitska_"+i, i, "Sarajevo", i, "BiH");
				JDBCUtil.setInsertAddress(address);
				genKeys = JDBCUtil.insertAddress.getGeneratedKeys();
				if (genKeys.next()) {
					addressId = genKeys.getInt(1);	
				}
				Library lib = new Library("Biblioteka_"+i, "033-120-240", "lib@lib.ba", address);
				JDBCUtil.setInsertLibrary(lib, addressId);
				genKeys = JDBCUtil.insertLibrary.getGeneratedKeys();
				if (genKeys.next()) {
					libraryId = genKeys.getInt(1);	
				}
				
				Author author1 = new Author("Peter_"+i, "Lacko", "Male",i);
				JDBCUtil.setInsertAuthor(author1);
				genKeys = JDBCUtil.insertAuthor.getGeneratedKeys();
				if (genKeys.next()) {
					authorId = genKeys.getInt(1);	
				}
				
				Category cat = new Category("Category_"+i);
				JDBCUtil.setInsertCategory(cat);
				genKeys = JDBCUtil.insertCategory.getGeneratedKeys();
				if (genKeys.next()) {
					categoryId = genKeys.getInt(1);	
				}
				
				for (int j = 0; j < 100; j++) {
					Member m = new Member("John_"+j, "Johns",i,"member@lib.ba");
					JDBCUtil.setInsertMember(m);
					JDBCUtil.insertMember.addBatch();
//					genKeysMember = JDBCUtil.insertMember.getGeneratedKeys();
//					if (genKeys.next()) {
//						memberId = genKeys.getInt(1);	
//					}
					
					Book book = new Book("Book_"+j, j, cat, author1);
					JDBCUtil.setInsertBook(book,authorId,categoryId);
					JDBCUtil.insertBook.addBatch();
//					genKeysBook = JDBCUtil.insertBook.getGeneratedKeys();
//					if (genKeys.next()) {
//						bookId = genKeys.getInt(1);	
//					}
					
					Employee emp = new Employee("Jane_"+j, "Janis", j, j, address);
					JDBCUtil.setInsertEmp(emp, addressId);
					JDBCUtil.insertEmployee.addBatch();
//					genKeysEmp = JDBCUtil.insertEmployee.getGeneratedKeys();
//					if (genKeys.next()) {
//						empId = genKeys.getInt(1);	
//					}
					
					lib.hireAnEmployee(emp);
//					JDBCUtil.setInsertLibraryEmployees(libraryId, empId);
					
					lib.registerAMember(m);
					m.setLibrary(lib);
//					JDBCUtil.setInsertLibraryMembers(libraryId, memberId);
					
					lib.addABook(book);					
//					JDBCUtil.setInsertLibraryBooks(libraryId, bookId);
					
					m.rentABook(book);
//					JDBCUtil.setInsertRentedBooks(memberId, bookId);
				}
				JDBCUtil.insertMember.executeBatch();
				JDBCUtil.insertBook.executeBatch();
				JDBCUtil.insertEmployee.executeBatch();
				genKeysMember = JDBCUtil.insertMember.getGeneratedKeys();
				genKeysBook = JDBCUtil.insertBook.getGeneratedKeys();
				genKeysEmp = JDBCUtil.insertEmployee.getGeneratedKeys();
				
				for (int j = 1; j <= 100; j++) {
					genKeysEmp.next();
					genKeysMember.next();
					genKeysBook.next();
					memberId = genKeysMember.getInt(1);
					JDBCUtil.setInsertLibraryEmployees(libraryId, genKeysEmp.getInt(1));
					JDBCUtil.setInsertLibraryMembers(libraryId,memberId);
					JDBCUtil.setInsertLibraryBooks(libraryId, genKeysBook.getInt(1));
					JDBCUtil.setInsertRentedBooks(memberId, genKeysBook.getInt(1));
				}
				
				if (i%50==0) {
					connection.commit();
				}
				
				
			}			
			connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;

		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	/*
	 *   QUERY ONE
	 */
	
	public long queryOne(int size){
		long elapsedTime = 0;
		String sql = "select b.title,b.year " +
				"from book b, rented_books rb, member m,category c " +
				"where b.category = c.id " +
				"and b.id = rb.book_id " +
				"and m.id = rb.member_id " +
				"and m.phone_number = ? " +
				"and b.year = ? " +
				"and c.name = ?";
		PreparedStatement selectStatement = null;
		double suma = 0;
		try {
			
			for (int i = 0; i < 4; i++) {
				long startTime = System.currentTimeMillis();
				selectStatement = connection.prepareStatement(sql);
				int parameter = getRandom(size / 100);
				selectStatement.setInt(1, parameter);
				selectStatement.setInt(2, getRandom(100));
				selectStatement.setString(3, "Category_" + parameter);
				ResultSet rs = selectStatement.executeQuery();
				if (rs.next()) {
					Book b = new Book();
					b.setTitle(rs.getString("title"));
					b.setYear(rs.getInt("year"));
					System.out.println(b.getTitle());
				}
				long stopTime = System.currentTimeMillis();
				elapsedTime = stopTime - startTime;
				suma += elapsedTime;
			}
			suma = suma/4;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (long) suma;
	}
	
	/*
	 *   QUERY TWO
	 */
	public long queryTwo(int size){
		long elapsedTime = 0;
		String sql = "select m.first_name,m.last_name,m.phone_number,m.email " +
				"from member m,rented_books rb, book b,category c " +
				"where b.category = c.id " +
				"and b.id = rb.book_id " +
				"and m.id = rb.member_id " +
				"and b.year = ? " +
				"and c.name = ?";
		PreparedStatement selectStatement = null;
		double suma = 0;
		try {
			
			for (int i = 0; i < 4; i++) {
				long startTime = System.currentTimeMillis();
				selectStatement = connection.prepareStatement(sql);
				selectStatement.setInt(1, getRandom(100));
				selectStatement.setString(2, "Category_"
						+ getRandom(size / 100));
				ResultSet rs = selectStatement.executeQuery();
				if (rs.next()) {
					Member m = new Member();
					m.setFirstName(rs.getString("first_name"));
					m.setLastName(rs.getString("last_name"));
					m.setPhoneNumber(rs.getInt("phone_number"));
					m.setEmail(rs.getString("email"));
					System.out.println("M:" + m.getFirstName());
				}
				long stopTime = System.currentTimeMillis();
				elapsedTime = stopTime - startTime;
				suma +=elapsedTime;
			}
			suma = suma/4;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (long)suma;
	}
	
	/*
	 *   QUERY THREE
	 */
	public long queryThree(int size){
		long elapsedTime = 0;
		String sql = "select l.name,l.phone_number,l.email " +
				"from library l, member m, library_books lb, library_employees le," +
				"library_members lm, book b,category c, employee e, address a " +
				"where b.category = c.id " +
				"and l.address = a.id " +
				"and b.id = lb.book_id " +
				"and l.id = lb.library_id " +
				"and m.id = lm.member_id " +
				"and l.id = lm.library_id " +
				"and e.id = le.employee_id " +
				"and l.id = le.library_id " +
				"and m.phone_number = ? " +
				"and c.name = ? " +
				"and a.house_number = ? " +
				"and e.salary = ? " +
				"and m.first_name = ? " +
				"and b.year = ?";
		PreparedStatement selectStatement = null;
		double suma = 0;
		try {
			
			for (int i = 0; i < 4; i++) {
				long startTime = System.currentTimeMillis();
				selectStatement = connection.prepareStatement(sql);
				int parameter = getRandom(size / 100);
				selectStatement.setInt(1, parameter);
				selectStatement.setString(2, "Category_" + parameter);
				selectStatement.setInt(3, parameter);
				selectStatement.setInt(4, getRandom(100));
				selectStatement.setString(5, "John_" + getRandom(100));
				selectStatement.setInt(6, getRandom(100));
				ResultSet rs = selectStatement.executeQuery();
				if (rs.next()) {
					Library l = new Library();
					l.setLibraryName(rs.getString("name"));
					l.setPhoneNumber(rs.getString("phone_number"));
					l.setEmail(rs.getString("email"));
					System.out.println("A:" + l.getLibraryName());
				}
				long stopTime = System.currentTimeMillis();
				elapsedTime = stopTime - startTime;
				suma +=elapsedTime;
			}
			suma = suma/4;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (long)suma;
	}
	
	/*
	 *   QUERY FOUR
	 */
	public long queryFour(int size){
		long elapsedTime = 0;
		String sql = "select m.first_name,m.last_name,m.phone_number,m.email " +
				"from member m,rented_books rb, book b,category c, library l," +
				" author a, address ad, library_members lm " +
				"where b.category = c.id " +
				"and b.id = rb.book_id " +
				"and m.id = rb.member_id " +
				"and m.id = lm.member_id " +
				"and l.id = lm.library_id " +
				"and ad.id = l.address " +
				"and a.id = b.author " + 
				"and m.phone_number = ? " +
				"and ad.house_number = ? " +
				"and a.age = ? " +
				"and b.year = ? " +
				"and c.name = ?";
		PreparedStatement selectStatement = null;
		double suma = 0;
		try {
			
			for (int i = 0; i < 4; i++) {
				long startTime = System.currentTimeMillis();
				selectStatement = connection.prepareStatement(sql);
				int parameter = getRandom(size / 100);
				selectStatement.setInt(1, parameter);
				selectStatement.setInt(2, parameter);
				selectStatement.setInt(3, parameter);
				selectStatement.setInt(4, getRandom(100));
				selectStatement.setString(5, "Category_" + parameter);
				ResultSet rs = selectStatement.executeQuery();
				if (rs.next()) {
					Member m = new Member();
					m.setFirstName(rs.getString("first_name"));
					m.setLastName(rs.getString("last_name"));
					m.setPhoneNumber(rs.getInt("phone_number"));
					m.setEmail(rs.getString("email"));
					System.out.println("M:" + m.getFirstName());
				}
				long stopTime = System.currentTimeMillis();
				elapsedTime = stopTime - startTime;
				suma +=elapsedTime;
			}
			suma = suma/4;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return (long)suma;
	}
	
	/*
	 *   UPDATE
	 */
	public long update(int size){
		long elapsedTime = 0;
		String sql = "update member m ,rented_books rb, book b,category c, library l," +
				" author a, address ad, library_members lm " +
				"set m.first_name = 'New_Name' " +
				"where b.category = c.id " +
				"and b.id = rb.book_id " +
				"and m.id = rb.member_id " +
				"and m.id = lm.member_id " +
				"and l.id = lm.library_id " +
				"and ad.id = l.address " +
				"and a.id = b.author " + 
				"and m.phone_number = ? " +
				"and ad.house_number = ? " +
				"and a.age = ? " +
				"and b.year = ? " +
				"and c.name = ? ";
		PreparedStatement selectStatement = null;
		try {
			
			long startTime = System.currentTimeMillis();
			
			connection.setAutoCommit(false);
			selectStatement = connection.prepareStatement(sql);
			int parameter = getRandom(size/100);
			selectStatement.setInt(1, parameter);
			selectStatement.setInt(2, parameter);
			selectStatement.setInt(3, parameter);
			selectStatement.setInt(4, getRandom(100));
			selectStatement.setString(5, "Category_" + parameter);
			int rs = selectStatement.executeUpdate();
			System.out.println("M:"+ rs);
            connection.commit();
			long stopTime = System.currentTimeMillis();
			elapsedTime = stopTime - startTime;			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return elapsedTime;
	}
	
	public void clearDB(){
		JDBCUtil.clearTable(connection, "library_books");
		JDBCUtil.clearTable(connection, "library_members");
		JDBCUtil.clearTable(connection, "library_employees");
		JDBCUtil.clearTable(connection, "rented_books");
		JDBCUtil.clearTable(connection, "library");
		JDBCUtil.clearTable(connection, "member");
		JDBCUtil.clearTable(connection, "employee");
		JDBCUtil.clearTable(connection, "book");
		JDBCUtil.clearTable(connection, "category");
		JDBCUtil.clearTable(connection, "author");		
		JDBCUtil.clearTable(connection, "address");
	}
	
	private int getRandom(int max){
		Random r = new Random();
        return r.nextInt(max); 
	}
	
	public void closeDatabase(){
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
